import mysql from 'mysql2';

// 数据库配置对象
const sqlConfig = {
    host: 'localhost', // 连接数据库的地址
    user: 'root',      // 用户名
    password: 'password', // 用户密码
    port: 3306,           // 端口号
    database: 'shop',     // 要使用的数据库名字
}
// 创建连接池
const pool = mysql.createPool(sqlConfig);
// 定义执行 SQL 语句的方法
function exec(sql: string) {
    // 返回一个promise对象
    return new Promise((resolve, reject) => {
        // 从连接池中获取一个连接
        pool.getConnection((err, connect) => {
            if (err) {
                reject(err);
            } else {
                // 执行SQL操作
                connect.query(sql, (error, result) => {
                    if (error) {
                        reject(error);
                    } else {
                        resolve({ result });
                    }
                });
            }
            // 释放连接
            connect?.release();
        });
    });
}

/**
 * 定义查询数据的方法
 * 可以指定查询的字段组
 */
const queryData = (table: string, keys: string[], where: string, callback:(data: any) => void) => {
    // 拼接完整的查询语句
    let keyString = '';
    // keys.join(',') ???
    keys.forEach((key) => {
        keyString += `${key},`;
    })
    keyString = keyString.substring(0, keyString.length - 1);
    let sql = `select ${keyString} from ${table} where ${where}`
    exec(sql).then(result => {
        callback(result);
    }).catch(() => {
        callback(null);
    })
}

// 定义插入数据的方法
const insertData = (table: string, keys: string[], values: any[], callback: (data: any) => void) => {
    // 拼接完整的SQL语句
    let keyString = '(';
    keys.forEach((key) => {
        keyString += `${key},`;
    });
    keyString = keyString.substring(0, keyString.length - 1);
    keyString += ')';
    let valueString = '(';
    values.forEach((value: any) => {
        if (typeof value === 'string') {
            valueString += `'${value}',`
        } else {
            valueString += `${value},`
        }
    });
    valueString = valueString.substring(0, valueString.length - 1);
    valueString += ')';
    let sql = `insert into ${table} ${keyString} values ${valueString}`;
    exec(sql).then(result => {
        callback(result);
    }).catch(() => {
        callback(null);
    });
}

const updateData = (table: string, updateString: string, where: string) => {
    let sql = `update ${table} set ${updateString} where ${where}`;
    exec(sql).then();
}

/**
 * 联表查询方法
 * mainTable: 主表名
 * mainTableKeys: 主表要查询的字段
 * otherKeys: 二维数组，每个元素为对应副表要查询的字段
 * otherTableName: 要查询的副表明数组，与othersKeys顺序对应
 * linkKeys: 主表数据中对应的副表的键名数组
 * linkValues: 最终要组合进结果数据的属性名数组
 */
const queryDataFrom = (mainTable: string, 
    mainTableKeys: string[], 
    othersKeys: string[][],
    otherTableName: string[],
    linkKeys: string[], 
    linkValues: string[], 
    where: string,
    callback: (data: any) => void) => {
        // 构建主表查询的字段字符串
        let keyString = '';
        mainTableKeys.forEach((key) => {
            keyString += `${key},`
        });
        keyString = keyString.substring(0, keyString.length - 1);
        // 构建副标查询的字符串
        let otherKeyString = '';
        let index = 0;
        // 遍历所有副表
        otherTableName.forEach((table) => {
            let otherString = '';
            // 副表要查询的字段
            let otherKeys = othersKeys[index];
            // 副表主键对应在主表中的字段名
            let linkKey = linkKeys[index];
            // 副表查询出的数据对应在结果对象中的属性名
            let linkValue = linkValues[index];
            // 遍历副表要查询的字段，构建语句
            otherKeys.forEach((otherK) => {
                if (otherString.length > 0) {
                    otherString += ','
                }
                otherString += `'${otherK}', ${table}.${otherK}`;
            });
            if (otherKeyString.length > 0) {
                otherKeyString += ',';
            }
            otherKeyString += `(select json_object(${otherString}) from ${table} ${table} where ${table}.id=o.${linkKey}) as ${linkValue}`;
            index++;
        });
        // 完整 SQL 语句拼接
        const sql = `select ${keyString}, ${otherKeyString} from ${mainTable} o where ${where}`;
        console.log(sql);
        exec(sql).then(result => {
            callback(result);
        }).catch((error) => {
            console.log('sqlError: ', error);
            callback(null);
        })
    }

// 删除方法
const deleteData = (table: string, where: string) => {
    const sql = `delete from ${table} where ${where}`;
    exec(sql).then();
}

// 导出工具方法
export default { queryData, insertData, updateData, queryDataFrom, deleteData };